package br.com.mundodaprata.store.data.jdbc;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import br.com.mundodaprata.store.data.UserRepository;
import br.com.mundodaprata.store.model.User;
import br.com.mundodaprata.store.model.UserType;

public class UserRepositoryJDBC extends RepositoryJDBC<User> implements UserRepository{

   @Override
   public void insert(User... elements) throws Exception {
      String sql = "insert into users(username, password, user_type) values(?, ?, ?);";
      
      PreparedStatement stmt = connection().prepareStatement(sql);
      
      try {
         for (User user : elements) {
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getType().toString());
            
            stmt.addBatch();
         }
         
         stmt.executeBatch();
      }
      finally {
         if (stmt != null && !stmt.isClosed()) {
            stmt.close();
         }
      }
   }

   @Override
   public void update(User... elements) throws Exception {
      String sql = "update users set username=?, password=?, user_type=? where code=?;";
      
      PreparedStatement stmt = connection().prepareStatement(sql);
      
      try {
         for (User user : elements) {
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getType().toString());
            stmt.setLong(4, user.getCode());
            
            stmt.addBatch();
         }
         
         stmt.executeBatch();
      }
      finally {
         if (stmt != null && !stmt.isClosed()) {
            stmt.close();
         }
      }
   }

   @Override
   public void remove(User... elements) throws Exception {
      String sql = "delete from users where code=?;";
      
      PreparedStatement stmt = connection().prepareStatement(sql);
      
      try {
         for (User user : elements) {
            stmt.setLong(1, user.getCode());
            
            stmt.addBatch();
         }
         
         stmt.executeBatch();
      }
      finally {
         if (stmt != null && !stmt.isClosed()) {
            stmt.close();
         }
      }
   }

   @Override
   public User select(Long key) {
      String sql = "select * from users where code = ?";
      
      try {
         PreparedStatement stmt = connection().prepareStatement(sql);
         stmt.setLong(1, key);
         
         try {
            ResultSet rs = stmt.executeQuery();
            
            if (rs.next()) {
               return toUser(rs);
            }
         }
         finally {
            if (stmt != null && !stmt.isClosed()) {
               stmt.close();
            }
         }
         
      }
      catch (SQLException e) {
         return null;
      }
      
      return null;
   }

   @Override
   public List<User> all() {
      String sql = "select * users;";
      
      try {
         PreparedStatement stmt = connection().prepareStatement(sql);
         
         try {
            ResultSet rs = stmt.executeQuery();
            List<User> result = new ArrayList<User>();
            
            while (rs.next()) {
               result.add(toUser(rs));
            }
            
            return result;
         }
         finally {
            if (stmt != null && !stmt.isClosed()) {
               stmt.close();
            }
         }
      }
      catch (SQLException e) {
         return Collections.emptyList();
      }
   }
   
   private User toUser(ResultSet rs) throws SQLException{
      User user = new User();
      
      user.setCode(rs.getLong("code"));
      user.setUsername(rs.getString("username"));
      user.setPassword(rs.getString("password"));
      user.setType(UserType.valueOf(rs.getString("user_type")));
      
      return user;
   }

   @Override
   public User selectByUsername(String username) {
      String sql = "select * from users where username = ?";
      
      try {
         PreparedStatement stmt = connection().prepareStatement(sql);
         stmt.setString(1, username);
         
         try {
            ResultSet rs = stmt.executeQuery();
            
            if (rs.next()) {
               return toUser(rs);
            }
         }
         finally {
            if (stmt != null && !stmt.isClosed()) {
               stmt.close();
            }
         }
         
      }
      catch (SQLException e) {
         return null;
      }
      
      return null;
   }
   
}